#!/usr/bin/env python
# -*- coding:utf-8 -*-

import pymysql
import openpyxl
import time
import os
import sys
import socket

timeout = 120
socket.setdefaulttimeout(timeout)


class Crawler:
    # 睡眠时长
    __time_sleep = 0.1
    __amount = 0
    __start_amount = 0
    __counter = 0
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0'}

    # 获取图片url内容等
    # t 下载图片时间间隔
    def __init__(self, t=0.1):
        self.time_sleep = t

    @staticmethod
    def make_excel(results, columns_name, filename, f):
        new_columns_name0 = ()
        for cn in columns_name:
            new_columns_name0 += cn
        new_columns_name = (new_columns_name0,) + results

        wb = openpyxl.Workbook(write_only=True)
        ws1 = wb.create_sheet()
        c = 1
        for col2 in new_columns_name:
            ws1.append(['%s' % (i is None) and "" or i for i in col2])
            sys.stdout.write("  已下载:%.0f%%" % (c / len(results) * 100) + '\r')
            sys.stdout.flush()
            c += 1
        if not os.path.exists('./' + f):
            os.mkdir('./' + f)

        f = './' + f + '/'
        wb.save(f + filename + '.xlsx')

    # 获取后缀名
    def get_data(self, name, f):
        # 打开数据库连接
        db = pymysql.connect("192.168.0.41", "py", "smw123", name)
        cursor = db.cursor()
        sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = 'women' and table_schema = '" + \
              name + "';"
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表x
        columns_name = cursor.fetchall()
        # print(columns_name)

        # 使用 cursor() 方法创建一个游标对象 cursor
        table = ('kid', 'kid_diff', 'men', 'men_diff', 'women', 'women_diff')
        for col in table:
            cursor2 = db.cursor()
            sql = "SELECT * FROM `" + col + "`;"
            # 执行SQL语句
            cursor2.execute(sql)
            # 获取所有记录列表x
            results = cursor2.fetchall()
            # print(results);exit()
            print("\r\n开始下载：" + col)
            self.make_excel(results, columns_name, col, f)
        db.close()


if __name__ == '__main__':
    crawler = Crawler(0.05)  # 抓取延迟为 0.05
    print("下载excel\r\n")
    db = pymysql.connect("192.168.0.41", "py", "smw123")
    cur = db.cursor()
    # 获取mysql中所有数据库
    cur.execute('SHOW DATABASES')
    db_name = cur.fetchall()
    db.close()
    print("当前可下载的库：")

    zd = []
    i = 0
    for col in db_name:
        if not col[0] == "information_schema":
            zd.append(col[0])
            i += 1
            print("    " + str(i) + "：" + col[0])
    # print(zd[3])

    n = input("\r\n请输入要下载的数据库名称对应的序号\r\n")
    while n == '' or (0 >= int(n) or int(n) > len(zd)):
        n = input("请输入要下载的数据库名称对应的序号\r\n")

    n = zd[int(n) - 1]
    f = input("请输入文件保存的文件夹名称\r\n")
    while f == '':
        f = input("请输入文件保存的文件夹名称\r\n")

    crawler.get_data(n, f)
    print("\r\n 下载结束\r\n")
